# 安装依赖：pip install pandas openpyxl tkinter tkcalendar
import tkinter as tk
from tkinter import messagebox, ttk
from tkcalendar import DateEntry  # 日期选择组件
import pandas as pd
from datetime import datetime
import json
import os

# ------------------- 全局变量 -------------------
data_list = []  # 存储录入的订单数据
DATA_FILE = "order_data.json"  # 数据文件路径
# Excel列顺序（与原始表格对齐）
COLUMNS = ["序号", "日期", "客户名称", "订单号", "订单金额", "已付金额", "未付金额"]


# ------------------- 数据存储函数 -------------------
def save_data():
    """保存数据到本地文件"""
    try:
        with open(DATA_FILE, 'w', encoding='utf-8') as f:
            json.dump(data_list, f, ensure_ascii=False, indent=2)
    except Exception as e:
        print(f"保存数据失败：{str(e)}")


def load_data():
    """从本地文件加载数据"""
    global data_list
    try:
        if os.path.exists(DATA_FILE):
            with open(DATA_FILE, 'r', encoding='utf-8') as f:
                data_list = json.load(f)
            print(f"成功加载 {len(data_list)} 条记录")
        else:
            print("数据文件不存在，使用空数据")
            data_list = []
    except Exception as e:
        print(f"加载数据失败：{str(e)}")
        data_list = []


# ------------------- 界面函数 -------------------
def refresh_table():
    """刷新数据表格显示"""
    # 清空表格
    for item in tree.get_children():
        tree.delete(item)
    
    # 插入数据
    for i, data in enumerate(data_list):
        unpaid = data["订单金额"] - data["已付金额"]
        tree.insert("", "end", values=(
            i + 1,  # 序号
            data["日期"],
            data["客户名称"],
            data["订单号"],
            f"¥ {data['订单金额']:,.0f}",
            f"¥ {data['已付金额']:,.0f}",
            f"¥ {unpaid:,.0f}"
        ))
    
    # 更新统计信息
    update_statistics()


def update_statistics():
    """更新右侧统计信息"""
    if not data_list:
        total_order_label.config(text="订单总额：¥ 0")
        total_paid_label.config(text="已付总额：¥ 0")
        total_unpaid_label.config(text="未付总额：¥ 0")
        
        # 清空客户统计
        customer_listbox.delete(0, tk.END)
        return
    
    # 计算总计
    total_order = sum(data["订单金额"] for data in data_list)
    total_paid = sum(data["已付金额"] for data in data_list)
    total_unpaid = total_order - total_paid
    
    # 更新总计标签
    total_order_label.config(text=f"订单总额：¥ {total_order:,.0f}")
    total_paid_label.config(text=f"已付总额：¥ {total_paid:,.0f}")
    total_unpaid_label.config(text=f"未付总额：¥ {total_unpaid:,.0f}")
    
    # 客户统计
    customer_stats = {}
    for data in data_list:
        customer = data["客户名称"]
        if customer not in customer_stats:
            customer_stats[customer] = {"订单": 0, "已付": 0, "未付": 0}
        
        customer_stats[customer]["订单"] += data["订单金额"]
        customer_stats[customer]["已付"] += data["已付金额"]
        customer_stats[customer]["未付"] += data["订单金额"] - data["已付金额"]
    
    # 更新客户统计列表
    customer_listbox.delete(0, tk.END)
    customer_listbox.insert(tk.END, "客户查询")
    for customer, stats in customer_stats.items():
        customer_listbox.insert(tk.END, f"{customer}")
        customer_listbox.insert(tk.END, f"  订单总额：¥ {stats['订单']:,.0f}")
        customer_listbox.insert(tk.END, f"  已付总额：¥ {stats['已付']:,.0f}")
        customer_listbox.insert(tk.END, f"  未付总额：¥ {stats['未付']:,.0f}")
        customer_listbox.insert(tk.END, "")


def submit_data():
    """提交订单数据"""
    try:
        # 获取输入值
        date = date_entry.get()  # 格式：yyyy/mm/dd
        customer = customer_entry.get().strip()
        order_no = order_no_entry.get().strip()
        order_amt = float(order_amt_entry.get())  # 转换为数值
        paid_amt = float(paid_amt_entry.get())
        
        # 验证非空
        if not (date and customer and order_no and order_amt and paid_amt):
            messagebox.showerror("错误", "请填写完整信息！")
            return
        
        # 封装数据
        data_list.append({
            "日期": date,
            "客户名称": customer,
            "订单号": order_no,
            "订单金额": order_amt,
            "已付金额": paid_amt
        })
        
        # 保存数据到文件
        save_data()
        
        # 清空输入框
        date_entry.set_date(datetime.now())  # 重置为当前日期
        customer_entry.delete(0, tk.END)
        order_no_entry.delete(0, tk.END)
        order_amt_entry.delete(0, tk.END)
        paid_amt_entry.delete(0, tk.END)
        
        # 刷新表格显示
        refresh_table()
        
        messagebox.showinfo("成功", "数据提交成功！")
        
    except ValueError:
        messagebox.showerror("错误", "金额需输入数字！")
    except Exception as e:
        messagebox.showerror("错误", f"提交失败：{str(e)}")


def delete_selected():
    """删除选中的记录"""
    selection = tree.selection()
    if not selection:
        messagebox.showwarning("警告", "请选择要删除的记录！")
        return
    
    if messagebox.askyesno("确认", "确定要删除选中的记录吗？"):
        # 获取选中项的索引
        item = selection[0]
        index = tree.index(item)
        
        # 删除数据
        del data_list[index]
        
        # 保存数据到文件
        save_data()
        
        # 刷新表格
        refresh_table()
        
        messagebox.showinfo("成功", "记录删除成功！")


def edit_selected():
    """编辑选中的记录"""
    selection = tree.selection()
    if not selection:
        messagebox.showwarning("警告", "请选择要编辑的记录！")
        return
    
    try:
        # 获取输入值
        date = date_entry.get()
        customer = customer_entry.get().strip()
        order_no = order_no_entry.get().strip()
        order_amt = float(order_amt_entry.get())
        paid_amt = float(paid_amt_entry.get())
        
        # 验证非空
        if not (date and customer and order_no and order_amt and paid_amt):
            messagebox.showerror("错误", "请填写完整信息！")
            return
        
        # 获取选中项的索引并更新数据
        item = selection[0]
        index = tree.index(item)
        
        data_list[index] = {
            "日期": date,
            "客户名称": customer,
            "订单号": order_no,
            "订单金额": order_amt,
            "已付金额": paid_amt
        }
        
        # 保存数据到文件
        save_data()
        
        # 刷新表格
        refresh_table()
        
        # 清空输入框
        date_entry.set_date(datetime.now())
        customer_entry.delete(0, tk.END)
        order_no_entry.delete(0, tk.END)
        order_amt_entry.delete(0, tk.END)
        paid_amt_entry.delete(0, tk.END)
        
        messagebox.showinfo("成功", "记录编辑成功！")
        
    except ValueError:
        messagebox.showerror("错误", "金额需输入数字！")
    except Exception as e:
        messagebox.showerror("错误", f"编辑失败：{str(e)}")


def on_tree_select(event):
    """表格选择事件"""
    selection = tree.selection()
    if selection:
        item = selection[0]
        index = tree.index(item)
        data = data_list[index]
        
        # 在输入框中显示选中的数据（用于编辑）
        date_entry.set_date(datetime.strptime(data["日期"], "%Y/%m/%d"))
        customer_entry.delete(0, tk.END)
        customer_entry.insert(0, data["客户名称"])
        order_no_entry.delete(0, tk.END)
        order_no_entry.insert(0, data["订单号"])
        order_amt_entry.delete(0, tk.END)
        order_amt_entry.insert(0, str(data["订单金额"]))
        paid_amt_entry.delete(0, tk.END)
        paid_amt_entry.insert(0, str(data["已付金额"]))


def export_excel():
    """导出数据到Excel（含汇总、客户统计和公式联动）"""
    if not data_list:
        messagebox.showerror("错误", "暂无数据可导出！")
        return
    
    try:
        from openpyxl import Workbook
        from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
        from openpyxl.utils import get_column_letter
        
        # 创建工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = "应收账款统计表"
        
        # 设置样式
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        border = Border(
            left=Side(border_style="thin"),
            right=Side(border_style="thin"),
            top=Side(border_style="thin"),
            bottom=Side(border_style="thin")
        )
        
        # 1. 写入表头（第1行）
        headers = ["序号", "日期", "客户名称", "订单号", "订单金额", "已付金额", "未付金额"]
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col)
            cell.value = header
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal="center")
            cell.border = border
        
        # 2. 写入数据（从第2行开始）
        for i, data in enumerate(data_list, 2):
            ws.cell(row=i, column=1).value = i - 1  # 序号
            ws.cell(row=i, column=2).value = data["日期"]
            ws.cell(row=i, column=3).value = data["客户名称"]
            ws.cell(row=i, column=4).value = data["订单号"]
            ws.cell(row=i, column=5).value = data["订单金额"]
            ws.cell(row=i, column=6).value = data["已付金额"]
            # 使用公式计算未付金额（实现联动）
            ws.cell(row=i, column=7).value = f"=E{i}-F{i}"
            
            # 设置边框
            for col in range(1, 8):
                ws.cell(row=i, column=col).border = border
        
        # 3. 写入汇总统计（I列开始）
        last_row = len(data_list) + 1
        
        # 汇总统计标题
        ws.cell(row=1, column=9).value = "汇总统计"
        ws.cell(row=1, column=9).font = header_font
        ws.cell(row=1, column=9).fill = header_fill
        ws.cell(row=1, column=9).alignment = Alignment(horizontal="center")
        
        # 订单总额（使用SUM公式）
        ws.cell(row=2, column=9).value = "订单总额"
        ws.cell(row=2, column=10).value = f"=SUM(E2:E{last_row})"
        
        # 已付总额（使用SUM公式）
        ws.cell(row=3, column=9).value = "已付总额"
        ws.cell(row=3, column=10).value = f"=SUM(F2:F{last_row})"
        
        # 未付总额（使用SUM公式）
        ws.cell(row=4, column=9).value = "未付总额"
        ws.cell(row=4, column=10).value = f"=SUM(G2:G{last_row})"
        
        # 4. 写入客户统计（L列开始）
        ws.cell(row=1, column=12).value = "客户名称"
        ws.cell(row=1, column=13).value = "订单总额"
        ws.cell(row=1, column=14).value = "已付总额"
        ws.cell(row=1, column=15).value = "未付总额"
        
        # 设置客户统计标题样式
        for col in range(12, 16):
            cell = ws.cell(row=1, column=col)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal="center")
            cell.border = border
        
        # 获取唯一客户名称
        customers = list(set(data["客户名称"] for data in data_list))
        
        # 为每个客户生成统计（使用SUMIF公式）
        for i, customer in enumerate(customers, 2):
            ws.cell(row=i, column=12).value = customer
            # 使用SUMIF公式计算该客户的订单总额
            ws.cell(row=i, column=13).value = f'=SUMIF(C2:C{last_row},"{customer}",E2:E{last_row})'
            # 使用SUMIF公式计算该客户的已付总额
            ws.cell(row=i, column=14).value = f'=SUMIF(C2:C{last_row},"{customer}",F2:F{last_row})'
            # 使用SUMIF公式计算该客户的未付总额
            ws.cell(row=i, column=15).value = f'=SUMIF(C2:C{last_row},"{customer}",G2:G{last_row})'
            
            # 设置边框
            for col in range(12, 16):
                ws.cell(row=i, column=col).border = border
        
        # 5. 调整列宽
        column_widths = {
            'A': 8,   # 序号
            'B': 12,  # 日期
            'C': 12,  # 客户名称
            'D': 12,  # 订单号
            'E': 15,  # 订单金额
            'F': 15,  # 已付金额
            'G': 15,  # 未付金额
            'I': 12,  # 汇总统计标签
            'J': 15,  # 汇总统计值
            'L': 12,  # 客户名称
            'M': 15,  # 客户订单总额
            'N': 15,  # 客户已付总额
            'O': 15   # 客户未付总额
        }
        
        for col, width in column_widths.items():
            ws.column_dimensions[col].width = width
        
        # 6. 设置数值格式
        for row in range(2, len(data_list) + 2):
            # 金额列设置为货币格式
            for col in [5, 6, 7]:  # E, F, G列
                ws.cell(row=row, column=col).number_format = '¥#,##0'
        
        # 汇总统计金额格式
        for row in range(2, 5):
            ws.cell(row=row, column=10).number_format = '¥#,##0'
        
        # 客户统计金额格式
        for row in range(2, len(customers) + 2):
            for col in range(13, 16):
                ws.cell(row=row, column=col).number_format = '¥#,##0'
        
        # 保存文件
        filename = "应收账款统计表.xlsx"
        wb.save(filename)
        
        messagebox.showinfo("成功", f"导出完成！文件：{filename}\n\n功能特点：\n• 未付金额自动计算（公式联动）\n• 汇总统计实时更新\n• 客户统计自动分组\n• 专业的表格样式")
    
    except Exception as e:
        messagebox.showerror("错误", f"导出失败：{str(e)}")


# ------------------- 界面搭建 -------------------
root = tk.Tk()
root.title("应收账款统计表")
root.geometry("1200x700")
root.configure(bg="#E8F5E8")

# 创建主框架
main_frame = ttk.Frame(root)
main_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)

# 左侧输入区域
input_frame = ttk.LabelFrame(main_frame, text="订单录入", padding=10)
input_frame.grid(row=0, column=0, sticky="nsew", padx=(0, 5))

# 组件：日期
date_label = ttk.Label(input_frame, text="日期：")
date_entry = DateEntry(
    input_frame, date_pattern="yyyy/mm/dd",  # 匹配表格日期格式
    background="#FFFFFF", foreground="#333333", borderwidth=1, width=15
)
date_entry.set_date(datetime.now())  # 默认当前日期

# 组件：客户名称
customer_label = ttk.Label(input_frame, text="客户名称：")
customer_entry = ttk.Entry(input_frame, width=20)

# 组件：订单号
order_no_label = ttk.Label(input_frame, text="订单号：")
order_no_entry = ttk.Entry(input_frame, width=20)

# 组件：订单金额
order_amt_label = ttk.Label(input_frame, text="订单金额：")
order_amt_entry = ttk.Entry(input_frame, width=20)

# 组件：已付金额
paid_amt_label = ttk.Label(input_frame, text="已付金额：")
paid_amt_entry = ttk.Entry(input_frame, width=20)

# 按钮框架
button_frame = ttk.Frame(input_frame)

# 按钮：提交、编辑、删除、导出
submit_btn = ttk.Button(button_frame, text="提交数据", command=submit_data)
edit_btn = ttk.Button(button_frame, text="编辑选中", command=edit_selected)
delete_btn = ttk.Button(button_frame, text="删除选中", command=delete_selected)
export_btn = ttk.Button(button_frame, text="导出Excel", command=export_excel)

# 输入区域布局
date_label.grid(row=0, column=0, padx=5, pady=5, sticky="w")
date_entry.grid(row=0, column=1, padx=5, pady=5, sticky="w")
customer_label.grid(row=1, column=0, padx=5, pady=5, sticky="w")
customer_entry.grid(row=1, column=1, padx=5, pady=5, sticky="w")
order_no_label.grid(row=2, column=0, padx=5, pady=5, sticky="w")
order_no_entry.grid(row=2, column=1, padx=5, pady=5, sticky="w")
order_amt_label.grid(row=3, column=0, padx=5, pady=5, sticky="w")
order_amt_entry.grid(row=3, column=1, padx=5, pady=5, sticky="w")
paid_amt_label.grid(row=4, column=0, padx=5, pady=5, sticky="w")
paid_amt_entry.grid(row=4, column=1, padx=5, pady=5, sticky="w")

button_frame.grid(row=5, column=0, columnspan=2, pady=10)
submit_btn.pack(side=tk.LEFT, padx=5)
edit_btn.pack(side=tk.LEFT, padx=5)
delete_btn.pack(side=tk.LEFT, padx=5)
export_btn.pack(side=tk.LEFT, padx=5)

# 中间表格区域
table_frame = ttk.LabelFrame(main_frame, text="应收账款统计表", padding=10)
table_frame.grid(row=0, column=1, sticky="nsew", padx=5)

# 创建Treeview表格
columns = ("序号", "日期", "客户名称", "订单号", "订单金额", "已付金额", "未付金额")
tree = ttk.Treeview(table_frame, columns=columns, show="headings", height=15)

# 设置列标题和宽度
tree.heading("序号", text="序号")
tree.heading("日期", text="日期")
tree.heading("客户名称", text="客户名称")
tree.heading("订单号", text="订单号")
tree.heading("订单金额", text="订单金额")
tree.heading("已付金额", text="已付金额")
tree.heading("未付金额", text="未付金额")

tree.column("序号", width=50, anchor=tk.CENTER)
tree.column("日期", width=100, anchor=tk.CENTER)
tree.column("客户名称", width=100, anchor=tk.CENTER)
tree.column("订单号", width=100, anchor=tk.CENTER)
tree.column("订单金额", width=120, anchor=tk.E)
tree.column("已付金额", width=120, anchor=tk.E)
tree.column("未付金额", width=120, anchor=tk.E)

# 绑定选择事件
tree.bind("<<TreeviewSelect>>", on_tree_select)

# 添加滚动条
scrollbar = ttk.Scrollbar(table_frame, orient=tk.VERTICAL, command=tree.yview)
tree.configure(yscrollcommand=scrollbar.set)

# 表格布局
tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
scrollbar.pack(side=tk.RIGHT, fill=tk.Y)

# 右侧统计区域
stats_frame = ttk.LabelFrame(main_frame, text="汇总统计", padding=10)
stats_frame.grid(row=0, column=2, sticky="nsew", padx=(5, 0))

# 总计标签
total_order_label = ttk.Label(stats_frame, text="订单总额：¥ 0", font=("Arial", 12, "bold"))
total_paid_label = ttk.Label(stats_frame, text="已付总额：¥ 0", font=("Arial", 12, "bold"))
total_unpaid_label = ttk.Label(stats_frame, text="未付总额：¥ 0", font=("Arial", 12, "bold"))

total_order_label.pack(anchor="w", pady=5)
total_paid_label.pack(anchor="w", pady=5)
total_unpaid_label.pack(anchor="w", pady=5)

# 客户统计区域
customer_frame = ttk.LabelFrame(stats_frame, text="客户查询", padding=5)
customer_frame.pack(fill=tk.BOTH, expand=True, pady=(10, 0))

customer_listbox = tk.Listbox(customer_frame, height=20, font=("Arial", 10))
customer_scrollbar = ttk.Scrollbar(customer_frame, orient=tk.VERTICAL, command=customer_listbox.yview)
customer_listbox.configure(yscrollcommand=customer_scrollbar.set)

customer_listbox.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
customer_scrollbar.pack(side=tk.RIGHT, fill=tk.Y)

# 配置网格权重
main_frame.grid_columnconfigure(1, weight=1)
main_frame.grid_rowconfigure(0, weight=1)

# 加载数据并初始化表格
load_data()
refresh_table()

root.mainloop()